--exec sp_create_update_pcmdata 'G62492.2','qci','G62492.2.qci'
create proc sp_create_update_pcmdata (

@lot_number varchar(50),
@file_type varchar(50),
@file_path varchar(50) )

as
begin
     if( @lot_number is not null ) and (@file_type is not null ) and (@file_path is not null)
    begin
	declare @count int
	set @count = 0 
    	SELECT @count = COUNT(Lot_Number) from PCM_DATA where Lot_Number = @lot_number
	if( @count != 0 )
	begin
	  if ( @file_type = 'wat' )
          begin	
		update PCM_DATA set Wat_Path = @file_path where Lot_Number = @lot_number
	  end	
	  else if ( @file_type = 'wsm' )
          begin
		update PCM_DATA set Wsm_Path = @file_path where Lot_Number = @lot_number
	  end
  	  else if ( @file_type = 'qci' )
          begin	
		update PCM_DATA set Qci_Path = @file_path where Lot_Number = @lot_number
	  end
	
	end
	else
	begin
    	   if ( @file_type = 'wat' )
           begin			
		insert into PCM_DATA ( Lot_Number,Wat_Path,Wsm_Path,Qci_Path,Timestamp ) values ( @lot_number , @file_path, null, null, current_timestamp  )
	   end
	   else if ( @file_type = 'wsm' )
           begin			
		insert into PCM_DATA ( Lot_Number,Wat_Path,Wsm_Path,Qci_Path,Timestamp ) values ( @lot_number , null, @file_path, null, current_timestamp  )
	   end
  	   else if ( @file_type = 'qci' )
           begin			
		insert into PCM_DATA ( Lot_Number,Wat_Path,Wsm_Path,Qci_Path,Timestamp ) values ( @lot_number , null, null, @file_path,  current_timestamp  )		
	   end
	end
    end	
   	
end
	